package org.zjvis.datascience.common.constant;

/**
 * @description 数据库常量
 * @create 2020-08-03
 */
public class DatabaseConstant {

    public static final long GREEN_PLUM_DATASET_ID = 1L;
    public static final String GREEN_PLUM_DEFAULT_SCHEMA = "dataset";

    public static final String DATA_IMPORT_TASK_POOL = "DATA_IMPORT_TASK_POOL";
    // 进度 *100
    public static final int DATA_IMPORT_PROGRESS_FINISH = 10000;
    public static final int DATA_IMPORT_PROGRESS_INIT = 0;

    /* 获取数据总条数(表加索引总大小)，表大小用:pg_table_size,索引大小用：pg_indexes_size */
    public static final String GP_SELECT_TOTAL_RELATION_SIZE = "select pg_size_pretty(pg_total_relation_size('%s.%s')) as size;";
    public static final String GP_SELECT_TABLE_DATA_COUNT = "select count(*) from %s.%s;";
    public static final String GP_COUNT_SQL = "select count(1) from %s;";
    public static final String GP_SELECT_10 = "select * from  %s.%s limit 10;";
    public static final String GP_SELECT_SQL = "select * from  %s.%s %s %s;";
    public static final String GP_SELECT_COL_SQL = "select %s from %s %s %s";
    public static final String GP_ORDER_SQL = " order by %s %s ";
    public static final String GP_LIMIT_SQL = " limit %s ";
    public static final String GP_LIMIT_OFFSET_SQL = " limit %s offset %s";
    public static final int GP_PREVIEW_COUNT = 20;

    /**
     * MYSQL
     */
    public static final String MYSQL_JDBC_URL = "jdbc:mysql://%s:%s/%s?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai";
    public static final String MYSQL_JDBC_URL_WITHOUT_DATABASE = "jdbc:mysql://%s:%s?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai";
    public static final String MYSQL_SHOW_DATABASES = "show databases;";
    public static final String MYSQL_SHOW_TABLES = "show tables;";

    /* Oracle */
    public static final String ORACLE_JDBC_SID_URL = "jdbc:oracle:thin:@%s:%s:%s";
    public static final String ORACLE_JDBC_SERVICE_NAME_URL = "jdbc:oracle:thin:@//%s:%s/%s";
    public static final String ORACLE_SHOW_DATABASES = "select name from v$database";
    public static final String ORACLE_SHOW_TABLES = "select table_name from user_tables";

    /* public */
    public static final String SQL_DROP_TABLE = "drop table if exists %s cascade;";


    /**
     * 连接数据库超时时间（秒）
     */
    public static final int LOGIN_TIMEOUT = 15;

    /**
     * 数据脱敏sql对应map的key
     */
    public static final String MAP_MASKING_SQL_KEY = "maskingSql";
    /**
     * 删表sql对应map的key
     */
    public static final String MAP_DROP_TABLE_SQL_KEY = "dropTableSql";
    /**
     * 表名对应map的key
     */
    public static final String MAP_TABLE_NAME_KEY = "tableName";

    /**
     * 密钥
     */
    public static final String SECRET_KEY = "YS5j93#%Wi8E@92Q";
    /**
     * 去除十六进制前缀hmac加密sql
     */
    public static final String HMAC_WITHOUT_HEX_PREFIX_SQL = "substr(hmac(%s::varchar,'%s','%s')::varchar, 3)";
    /**
     * 字符串打码sql，13个替换位置，同一个字段替换 （处理逻辑要和CryptoUtil.stringMosaic(String src)一致）
     */
    //substring方法的第一个参数是值，第二个是开始位置从1开始而非0（第一个字符下标是1），第三个参数是截取长度
    public static final String MOSAIC_SQL = "case when %s is null then '' when char_length(%s::varchar)<6 then '****'"
            + " when char_length(%s::varchar)>10 then substring(%s::varchar,1,3)||'****'||substring(%s::varchar,length(%s::varchar)-3,4)"
            + " else substring(%s::varchar,1,char_length(%s::varchar)/2-2)"
            + "||'****'||substring(%s::varchar,char_length(%s::varchar)/2+3,char_length(%s::varchar)-char_length(%s::varchar)/2-2)"
            + " end %s ,";
    /**
     * 字段为null时，替换成成0
     */
    public static final String COALESCE_ZERO_SQL = "coalesce(%s,0) %s,";
    /**
     * 字段为null时，替换成成空字符串
     */
    public static final String COALESCE_EMPTY_STR_SQL = "coalesce(%s,'') %s,";
    /**
     * SQL查询时，显示数据数目
     */
    public static final int GP_SQL_PREVIEW_COUNT = 5000;
    /**
     * SQL查询时，导出数据最大数量
     */
    public static final int GP_SQL_EXPORT_COUNT = 100000;
    /**
     * SQL查询时，导出数据文件名称
     */
    public static final String GP_SQL_EXPORT_NAME = "QueryResult";


}
